package util;

import entity.Book;
import entity.Client;
import entity.Goods;
import entity.User;

import java.sql.*;
import java.util.Date;

//boolean execute(String SQL) : 如果 ResultSet 对象可以被检索，则返回的布尔值为 true ，否则返回 false 。当你需要使用真正的动态 SQL 时，可以使用这个方法来执行 SQL DDL 语句。
//int executeUpdate(String SQL) : 返回执行 SQL 语句影响的行的数目。使用该方法来执行 SQL 语句，是希望得到一些受影响的行的数目，例如，INSERT，UPDATE 或 DELETE 语句。
//ResultSet executeQuery(String SQL) : 返回一个 ResultSet 对象。当你希望得到一个结果集时使用该方法，就像你使用一个 SELECT 语句。


public class JdbcDeal implements JdbcConfig {
    private Connection connection= null;
    private PreparedStatement psmt = null;
    private Statement stmt = null;
    private ResultSet resultSet = null;
    CallableStatement callableStatement;

    public JdbcDeal() {
        try {
            Class.forName(DRIVER);
            connection = DriverManager.getConnection(URL,USERNAME,PASSWORD);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    public void close() throws SQLException{

        try {
            if(resultSet != null){
                resultSet.close();
            }
            if(psmt != null){
                psmt.close();
            }
            if(stmt != null){
                stmt.close();
            }
            connection.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

    }

    //修改
    public int Update(String sql) throws SQLException{
        stmt = connection.createStatement();
        return stmt.executeUpdate(sql);
    }

    //删除
    public int del(String sql) throws SQLException{
//        stmt = connection.prepareStatement(sql);
        stmt = connection.createStatement();
        return stmt.executeUpdate(sql);
    }

    //查询
    public ResultSet select(String sql) throws SQLException{
        psmt = connection.prepareStatement(sql);
        resultSet = psmt.executeQuery();
        return resultSet;
    }

    //员工登录
    public boolean IsLogin(User user) throws SQLException{
        String password = "";
        if(user.isFlag()){
            String sql = "SELECT * FROM ad WHERE Aaccount='" + user.getWorkNo() +"'";
            psmt = connection.prepareStatement(sql);
            resultSet = psmt.executeQuery();

            while (resultSet.next()){
                password = resultSet.getString("ApassWord");
            }
        }else{
            String sql = "SELECT * FROM worker WHERE Wno='" + user.getWorkNo() +"'";
            psmt = connection.prepareStatement(sql);
            resultSet = psmt.executeQuery();
            while(resultSet.next()){
                password = resultSet.getString("Wpassword");
            }
        }
        if(password.equals(user.getPassword())){
            return true;
        }else {
            return false;
        }
    }

    //员工注册（添加）
    public int Worker_add(User user) {

        try {
            psmt = connection.prepareStatement("INSERT INTO worker VALUES(?,?,?,?)");
            psmt.setString(1,user.getWorkNo());
            psmt.setString(2,user.getPassword());
            psmt.setString(3,user.getName());
            psmt.setString(4,user.getSex());

            return psmt.executeUpdate();
        } catch (SQLException throwables) {
            return 0;
        }
    }

    //客户添加
    public int Client_add(Client client) {

        try {
            psmt = connection.prepareStatement("INSERT INTO client VALUES(?,?,?)");
            psmt.setString(1,client.getClientNo());
            psmt.setString(2,client.getName());
            psmt.setString(3,client.getPhone());

            return psmt.executeUpdate();
        } catch (SQLException throwables) {
            return 0;
        }
    }

    //货物添加
    public int Goods_add(Goods goods) {

        try {
            psmt = connection.prepareStatement("INSERT INTO goods VALUES(?,?,?,?,?)");
            psmt.setString(1,goods.getGoodsNo());
            psmt.setString(2,goods.getGoodsort());
            psmt.setString(3,goods.getName());
            psmt.setString(4,goods.getPirce());
            psmt.setString(5,goods.getGnum());

            return psmt.executeUpdate();
        } catch (SQLException throwables) {
            return 0;
        }
    }

    //订单添加
    public int Book_add(Book book) throws SQLException{

        try {
            callableStatement = connection.prepareCall("{CALL bookadd (?,?,?,?,?,?)}");
            callableStatement.setString(1,book.getBookNo());
            callableStatement.setString(2,book.getWno());
            callableStatement.setString(3,book.getCno());
            callableStatement.setString(4,book.getGno());
            callableStatement.setInt(5,book.getBnum());
            callableStatement.setDate(6,new java.sql.Date(new Date().getTime()));
            System.out.println(callableStatement.execute());
            callableStatement.close();
            return 1;
        } catch (SQLException throwables) {
            callableStatement.close();
            throwables.printStackTrace();
            return 0;
        }
    }

    //订单删除
    public int Bookdel(Book book) throws SQLException{
        try {
            callableStatement = connection.prepareCall("{CALL bookremove (?,?,?)}");
            callableStatement.setString(1,book.getBookNo());
            callableStatement.setString(2,book.getGno());
            callableStatement.setInt(3,book.getBnum());
            System.out.println(callableStatement.execute());
            callableStatement.close();
            return 1;
        } catch (SQLException throwables) {
            callableStatement.close();
            throwables.printStackTrace();
            return 0;
        }
    }

    //订单修改
    public int Bookupdate(Book book,String lastGno,int lastNum) throws SQLException{
        System.out.println(book.getBookNo()+','+book.getCno()+','+lastGno+','+book.getGno()+','+lastNum+','+book.getBnum());
        try {
            callableStatement = connection.prepareCall("{CALL bookupdate (?,?,?,?,?,?)}");
            callableStatement.setString(1,book.getBookNo());
            callableStatement.setString(2,book.getCno());
            callableStatement.setString(3,lastGno);
            callableStatement.setString(4,book.getGno());
            callableStatement.setInt(5,lastNum);
            callableStatement.setInt(6,book.getBnum());
            System.out.println(callableStatement.execute());
            callableStatement.close();
            return 1;
        } catch (SQLException throwables) {
            callableStatement.close();
            throwables.printStackTrace();
            return 0;
        }
    }

    //类别添加
    public int Sort_add(String Sname) {
        String fileName= java.util.UUID.randomUUID().toString();

        try {
            psmt = connection.prepareStatement("INSERT INTO sort VALUES(?,?)");
            psmt.setString(1,fileName);
            psmt.setString(2,Sname);

            return psmt.executeUpdate();
        } catch (SQLException throwables) {
            return 0;
        }
    }


}
